package com.business.photo;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.beans.photo.PhotoDetail;
import com.sql.DBFactory;

public class PhotoListBus {

	private Connection conn = null;

	private Statement stmt = null;

	private ResultSet rs = null;

	private static final Log log = LogFactory.getLog(PhotoListBus.class);

	@SuppressWarnings("unchecked")
	public List getAllPhotoByUserName(String username) {
		List list = new ArrayList();
		String sql = "select * from photo_detail where username='" + username
				+ "'  order by add_time desc";
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				PhotoDetail pd = new PhotoDetail();
				pd.setIsShow(rs.getString("isShow"));
				pd.setPhotoId(rs.getString("photo_id"));
				pd.setAddTime(rs.getString("add_time"));
				pd.setPhotoId(rs.getString("photo_id"));
				pd.setPhotoMemo(rs.getString("photo_memo"));
				pd.setPhotoName(rs.getString("photo_name"));
				pd.setPhototypeId(rs.getString("phototype_id"));
				pd.setUsername(rs.getString("username"));
				pd.setPhotoUrl(rs.getString("photo_url"));
				pd.setReplyCount(rs.getString("reply_count"));
				pd.setViewCount(rs.getString("view_count"));
				pd.setPhotoNo(rs.getString("photo_no"));
				list.add(pd);
			}
		} catch (Exception e) {
			log.error("the select photodetail exception:" + e.getMessage());
			log.error("the select photodetail sql is: " + sql);
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	public List getAllPhotoByShow(String username) {
		List list = new ArrayList();
		String sql = "select * from photo_detail where username='" + username
				+ "' and isShow=1 order by add_time desc";
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				PhotoDetail pd = new PhotoDetail();
				pd.setIsShow(rs.getString("isShow"));
				pd.setPhotoId(rs.getString("photo_id"));
				pd.setAddTime(rs.getString("add_time"));
				pd.setPhotoId(rs.getString("photo_id"));
				pd.setPhotoMemo(rs.getString("photo_memo"));
				pd.setPhotoName(rs.getString("photo_name"));
				pd.setPhototypeId(rs.getString("phototype_id"));
				pd.setUsername(rs.getString("username"));
				pd.setPhotoUrl(rs.getString("photo_url"));
				pd.setReplyCount(rs.getString("reply_count"));
				pd.setViewCount(rs.getString("view_count"));
				pd.setPhotoNo(rs.getString("photo_no"));
				list.add(pd);
			}
		} catch (Exception e) {
			log.error("the select photodetail exception:" + e.getMessage());
			log.error("the select photodetail sql is: " + sql);
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return list;
	}
	@SuppressWarnings("unchecked")
	public List getAllPhotoByTypeID(int phototypeid) {
		List list = new ArrayList();
		String sql = "select * from photo_detail where phototype_id="
				+ phototypeid + " and isShow=1 order by add_time desc";
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				PhotoDetail pd = new PhotoDetail();
				pd.setIsShow(rs.getString("isShow"));
				pd.setPhotoId(rs.getString("photo_id"));
				pd.setAddTime(rs.getString("add_time"));
				pd.setPhotoId(rs.getString("photo_id"));
				pd.setPhotoMemo(rs.getString("photo_memo"));
				pd.setPhotoName(rs.getString("photo_name"));
				pd.setPhototypeId(rs.getString("phototype_id"));
				pd.setUsername(rs.getString("username"));
				pd.setPhotoUrl(rs.getString("photo_url"));
				pd.setReplyCount(rs.getString("reply_count"));
				pd.setViewCount(rs.getString("view_count"));
				pd.setPhotoNo(rs.getString("photo_no"));
				list.add(pd);

			}
		} catch (Exception e) {
			log.error("the select photodetail exception:" + e.getMessage());
			log.error("the select photodetail sql is: " + sql);
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return list;
	}

	public PhotoDetail getPhotoByNo(String photoNo) {
		PhotoDetail pd = new PhotoDetail();
		String sql = "select * from photo_detail where photo_no='" + photoNo
				+ "'";
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				pd.setIsShow(rs.getString("isShow"));
				pd.setPhotoId(rs.getString("photo_id"));
				pd.setAddTime(rs.getString("add_time"));
				pd.setPhotoId(rs.getString("photo_id"));
				pd.setPhotoMemo(rs.getString("photo_memo"));
				pd.setPhotoName(rs.getString("photo_name"));
				pd.setPhototypeId(rs.getString("phototype_id"));
				pd.setUsername(rs.getString("username"));
				pd.setPhotoUrl(rs.getString("photo_url"));
				pd.setReplyCount(rs.getString("reply_count"));
				pd.setViewCount(rs.getString("view_count"));
				pd.setPhotoNo(rs.getString("photo_no"));
			}
		} catch (Exception e) {
			log.error("the select photodetail exception:" + e.getMessage());
			log.error("the select photodetail sql is: " + sql);
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return pd;
	}

	public PhotoDetail getFrontPhoto(String username,String photoNo) {
		PhotoDetail pd = new PhotoDetail();
		String sql = "select * from photo_detail where username='" + username
					+ "' and isShow=1 and photo_no <'" + photoNo + "' order by photo_no";
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				pd.setIsShow(rs.getString("isShow"));
				pd.setPhotoId(rs.getString("photo_id"));
				pd.setAddTime(rs.getString("add_time"));
				pd.setPhotoId(rs.getString("photo_id"));
				pd.setPhotoMemo(rs.getString("photo_memo"));
				pd.setPhotoName(rs.getString("photo_name"));
				pd.setPhototypeId(rs.getString("phototype_id"));
				pd.setUsername(rs.getString("username"));
				pd.setPhotoUrl(rs.getString("photo_url"));
				pd.setReplyCount(rs.getString("reply_count"));
				pd.setViewCount(rs.getString("view_count"));
				pd.setPhotoNo(rs.getString("photo_no"));
			}
		} catch (Exception e) {
			log.error("the select photodetail exception:" + e.getMessage());
			log.error("the select photodetail sql is: " + sql);
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return pd;
	}

	public PhotoDetail getNextPhoto(String username,String photoNo) {
		PhotoDetail pd = new PhotoDetail();
		String sql = "select * from photo_detail where username='" + username
					+ "' and isShow=1 and photo_no >'" + photoNo + "' order by photo_no";
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				pd.setIsShow(rs.getString("isShow"));
				pd.setPhotoId(rs.getString("photo_id"));
				pd.setAddTime(rs.getString("add_time"));
				pd.setPhotoId(rs.getString("photo_id"));
				pd.setPhotoMemo(rs.getString("photo_memo"));
				pd.setPhotoName(rs.getString("photo_name"));
				pd.setPhototypeId(rs.getString("phototype_id"));
				pd.setUsername(rs.getString("username"));
				pd.setPhotoUrl(rs.getString("photo_url"));
				pd.setReplyCount(rs.getString("reply_count"));
				pd.setViewCount(rs.getString("view_count"));
				pd.setPhotoNo(rs.getString("photo_no"));
			}
		} catch (Exception e) {
			log.error("the select photodetail exception:" + e.getMessage());
			log.error("the select photodetail sql is: " + sql);
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return pd;
	}

	public void addViewCount(String photoNo) {
		PhotoDetail pd=getPhotoByNo(photoNo);
		int viewnums=Integer.parseInt(pd.getViewCount());
		viewnums++;
		String sql = "update photo_detail set view_count=" + viewnums
				+ " where photo_no='" + photoNo + "'";
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (Exception e) {
			log.error("the update photodetail exception:" + e.getMessage());
			log.error("the update photodetail sql is: " + sql);
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		
	}

}
